package cn.tedu.jdbc;

import java.sql.*;
import java.util.Scanner;

//用户登录测试
public class Test3 {
    public static void main(String[] args) throws Exception {
        System.out.println("请输入用户名");
        String a = new Scanner(System.in).nextLine();
        //jack'#注释掉密码，不输入密码，也可登录成功
        //          jack' or 1=1
        //sql攻击/SQL注入问题：本质用户输入特殊的符号#  造成SQL语义发生了改变
        System.out.println("请输入密码");
        String b = new Scanner(System.in).nextLine();
//        method(a,b);
//        method();
        method1(a, b);

    }

    //用户名和密码表查询user_1表
    private static void method(String name, String password) throws Exception {
//    private static void method()throws Exception {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
//        String url="jdbc:mysql://localhost:3306/abc?characterEncoding=utf8";
        String url = "jdbc:mysql:///abc?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root105");
        //获取传输器
        Statement st = conn.createStatement();
        //执行sql，根据用户名和密码查库
//        String name=new Scanner(System.in).nextLine();
//        String password=new Scanner(System.in).nextLine();
        String sql = "SELECT * FROM user_1 WHERE name = '" + name + "' and password ='" + password + "'";
        ResultSet rs = st.executeQuery(sql);
        //分析结果集
        if (rs.next()) {
//如果查到了数据，说明SELECT * FROM user_1 WHERE name = 'jack' and password ='12'查到了数据，登录成功
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //释放资源（注意顺序）
        rs.close();//结果集
        st.close();//传输器
        conn.close();//连接

    }

    private static void method1(String name, String password) throws Exception {
//    private static void method()throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
//        String url="jdbc:mysql://localhost:3306/abc?characterEncoding=utf8";
        String url = "jdbc:mysql:///abc?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root105");
        //3.获取传输器
//        Statement st=conn.createStatement()
        String sql = "SELECT * FROM user_1 WHERE name =? and password =?";
        //'jack\'#'
        //没有拼接，占位符
        //PreparedStatement把SQL骨架和参数分开发送给数据的，？叫占位符
        PreparedStatement ps = conn.prepareStatement(sql);//
        //给SQL设置参数，指定给那个参数赋什么值
        ps.setString(1, name);
        ps.setString(2, password);
        //4.执行sql，根据用户名和密码查库
//        String name=new Scanner(System.in).nextLine();
//        String password=new Scanner(System.in).nextLine();
//        String sql="SELECT * FROM user_1 WHERE name = '"+name+"' and password ='"+password+"'";
//        ResultSet rs=st.executeQuery(sql);
        ResultSet rs = ps.executeQuery();
        //5.分析结果集
        if (rs.next()) {
//如果查到了数据，说明SELECT * FROM user_1 WHERE name = 'jack' and password ='12'查到了数据，登录成功
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //6.释放资源（注意顺序）
        rs.close();//结果集
        ps.close();//传输器
        conn.close();//连接

    }
}
